package com.app.TCES.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.app.TCES.domain.Class;
import com.app.TCES.domain.Department;
import com.app.TCES.domain.Student;
import com.app.TCES.domain.Student_Dept_Class;

@Mapper
public interface A_studentMapper {

	// 查询学生信息
	@Select("select stuId,stuName,className,deptName from student s,class c,department d where s.deptId=d.deptId and s.classId=c.classId limit #{tmp},#{limit}")
	public List<Student_Dept_Class> SelectStudents(@Param("tmp")Integer tmp,@Param("limit")Integer limit);
	
	//查询所有学生条数
	@Select("select count(*) from student")
	public int total();
	
	// 查询学院(admin_teacher)
	@Select("select * from department")
	public List<Department> selectdept();
	
	//点击学院按钮查询学生
	@Select("select stuId,stuName,className,deptName from student s,class c,department d where s.deptId=d.deptId and s.classId=c.classId and d.deptName=#{deptName} limit #{tmp},#{limit}")
	public List<Student_Dept_Class> selectDept_Student(@Param("tmp")Integer tmp,@Param("limit")Integer limit, @Param("deptName")String deptName);
	
	//点击学院按钮查询学生条数
	@Select("select count(*) from student s,department d where s.deptId=d.deptId and d.deptName=#{deptName}")
	public int total1(@Param("deptName")String deptName);
	
	//通过院系(Name)查班级
	@Select("select * from class c,department d where c.deptId=d.deptId and d.deptName=#{deptName}")
	public List<Class> selectClasses(@Param("deptName")String deptName);
	
	
	//通过院系(Id)查班级
	@Select("select * from class c,department d where c.deptId=d.deptId and d.deptId=#{deptId}")
	public List<Class> selectC(@Param("deptId")int deptId);
	
	//通过班级(Name)按钮查学生
	@Select("select stuId,stuName,className,deptName from student s,class c,department d where s.deptId=d.deptId and s.classId=c.classId and c.className=#{className} limit #{tmp},#{limit}")
	public List<Student_Dept_Class> selectClass_Studnet(@Param("tmp")Integer tmp,@Param("limit")Integer limit,@Param("className")String className);
	
	//通过班级按钮查学生条数
	@Select("select count(*) from student s,class c,department d where s.deptId=d.deptId and s.classId=c.classId and c.className=#{className}")
	public int total2(@Param("className")String className);
	
	//修改学生信息(先找到学院ID和班级ID)
	@Update("update student set deptId=#{deptId},classId=#{classId} where stuId=#{stuId}")
	public void updateStudent(@Param("deptId")int deptId,@Param("classId")int classId,@Param("stuId")int stuId);
	
	//删除学生数据
	@Delete("delete from student where stuId = #{stuId}")
	public void delStudent(@Param("stuId")int stuId);
	
	//添加学生(验证学号是否重复)
	@Select("select * from student where stuId=#{stuId}")
	public List<Student> validation_StuId(@Param("stuId")int stuId);
	
	//添加学生
	@Insert("insert into student values(#{stuId},#{stuName},#{password},#{deptId},#{classId})")
	public void insertStudent(@Param("stuId")int stuId,@Param("stuName")String stuName,@Param("password")String password,@Param("deptId")int deptId,@Param("classId")int classId);

	//模糊查询1
	@Select("select * from student s,class c,department d where s.deptId=d.deptId and s.classId=c.classId and ((stuId like CONCAT('%',#{msg},'%')) or (stuName like CONCAT('%',#{msg},'%')) or (deptName like CONCAT('%',#{msg},'%')) or (className like CONCAT('%',#{msg},'%')) ) limit #{tmp},#{limit}")
	public List<Student_Dept_Class> FQ1(@Param("tmp")Integer tmp,@Param("limit")Integer limit,@Param("msg")String msg);
	
	//模糊查询2
	@Select("select * from student s,class c,department d where s.deptId=d.deptId and s.classId=c.classId and deptName=#{deptName} and ((stuId like CONCAT('%',#{msg},'%')) or (stuName like CONCAT('%',#{msg},'%')) or (deptName like CONCAT('%',#{msg},'%')) or (className like CONCAT('%',#{msg},'%')) ) limit #{tmp},#{limit}")
	public List<Student_Dept_Class> FQ2(@Param("tmp")Integer tmp,@Param("limit")Integer limit,@Param("msg")String msg,@Param("deptName")String deptName);
	
	
	//模糊查询3
	@Select("select * from student s,class c,department d where s.deptId=d.deptId and s.classId=c.classId and deptName=#{deptName} and className=#{className} and ((stuId like CONCAT('%',#{msg},'%')) or (stuName like CONCAT('%',#{msg},'%')) or (deptName like CONCAT('%',#{msg},'%')) or (className like CONCAT('%',#{msg},'%')) ) limit #{tmp},#{limit}")
	public List<Student_Dept_Class> FQ3(@Param("tmp")Integer tmp,@Param("limit")Integer limit,@Param("msg")String msg,@Param("deptName")String deptName,@Param("className")String className);
	
	
	//模糊查询后分页1
	@Select("select count(*) from student s,class c,department d where s.deptId=d.deptId and s.classId=c.classId and ((stuId like CONCAT('%',#{msg},'%')) or (stuName like CONCAT('%',#{msg},'%')) or (deptName like CONCAT('%',#{msg},'%')) or (className like CONCAT('%',#{msg},'%'))  )")
	public int total3(@Param("msg")String msg);
	
	
	//模糊查询后分页2
	@Select("select count(*) from student s,class c,department d where s.deptId=d.deptId and s.classId=c.classId and deptName=#{deptName} and ((stuId like CONCAT('%',#{msg},'%')) or (stuName like CONCAT('%',#{msg},'%')) or (deptName like CONCAT('%',#{msg},'%')) or (className like CONCAT('%',#{msg},'%'))  )")
	public int total4(@Param("msg")String msg,@Param("deptName")String deptName);
	
	//模糊查询后分页3
	@Select("select count(*) from student s,class c,department d where s.deptId=d.deptId and s.classId=c.classId and deptName=#{deptName} and className=#{className} and ((stuId like CONCAT('%',#{msg},'%')) or (stuName like CONCAT('%',#{msg},'%')) or (deptName like CONCAT('%',#{msg},'%')) or (className like CONCAT('%',#{msg},'%'))  )")
	public int total5(@Param("msg")String msg,@Param("deptName")String deptName,@Param("className")String className);
	

	//密码重置
	@Update("update student set password=#{password} where stuId=#{stuId}")
	public void RP(@Param("password")String password,@Param("stuId")int stuId);
}
